#coding=utf-8
#测试场景：遍历测试数据库
#创建者：陈强
#创建日期：2019/04/01
import os
import sys
project = 'TestWeb'  # 工作项目根目录
sys.path.append(os.getcwd().split(project)[0] + project)
import hashlib
import xlrd
from TestWeb import mysql_password
from xlutils.copy import copy



def create_password(phone,pwd):
    salt = mysql_password.password(phone)[3]
    m = hashlib.md5()  # 创建md5对象
    m.update(pwd)  # 生成加密串，其中password是要加密的字符串,第一次加密
    pwd1 = m.hexdigest()  # 打印经过md5加密的字符串
    pwd = hashlib.md5()  # 第二次加密
    pwd.update(str(pwd1) + salt)
    new_password = pwd.hexdigest()
    return str(new_password)

def handler_excel(sheet,filename=r'D:\\python-photo\\data\\login_data.xls'):
    # 打开文件
    workbook = xlrd.open_workbook(filename)
    table = workbook.sheets()[sheet]
    # 遍历
    nrows = table.nrows  #行数
    ncols = table.ncols  #列数
    phone_list = []
    password_list = []
    expected_list = []
    authCode_list = []
    for i in range(1,nrows):
        data_dict = {}
        phone = table.row_values(i)[0]
        password = table.row_values(i)[1]
        expected = table.row_values(i)[2]
        data_dict['phone'] = phone
        data_dict['expected'] = expected
        if sheet == 0:
            new_password = create_password(phone,password)
            data_dict['password'] = new_password
        if sheet == 1:
            authCode = table.row_values(i)[3]
            data_dict['authCode'] = authCode
            authCode_list.append(data_dict)
            data_dict['password'] = password
        phone_list.append(data_dict)
        password_list.append(data_dict)
        expected_list.append(data_dict)
    return phone_list,nrows  #返回字典数组，第一个为预期结果，第二个为手机号码，第三个为密码，第四个为邀请码或付款码,行数

def write_excel(actual_result,result,row,sheet,filename=r'D:\\python-photo\\data\\login_data.xls'):
    workbook = xlrd.open_workbook(filename)
    newWb = copy(workbook)
    if sheet == 0:
        sheet = newWb.get_sheet(0)
        sheet.write(row, 3, actual_result)
        sheet.write(row, 4, result)
    if sheet == 1:
        sheet = newWb.get_sheet(1)
        sheet.write(row, 4, actual_result)
        sheet.write(row, 5, result)
    os.remove(filename)
    newWb.save(filename)

def write_phone_excel(mobile,row,filename=r'D:\\python-photo\\data\\login_data.xls'):
    workbook = xlrd.open_workbook(filename)
    newWb = copy(workbook)
    sheet = newWb.get_sheet(1)
    sheet.write(row, 0, mobile)
    os.remove(filename)
    newWb.save(filename)




if __name__ == '__main__':
    nrows = handler_excel(0)[0][0].values()
    print nrows
    # create_password('17781108473','cq123321')
    # write_excel('PASS','FAIL',1,0)

